Alphabetical pagination

I am trying to make a page that displays a-z like a b c d e etc as links
then when you click open one it reloads itself and shows only the query
results that go with that letter...i'm not getting it....I get a page that
says ARRAY over and over...

What I have so far:

if(!isset($_SESSION['RestaurantList']))

{
// List not grabbed yet, so run
query and store in $_SESSION

$sql =3D "SELECT
DISTINCT name FROM restaurants GROUP BY name DESC";

$result =3D mysql_query($sql) or die(mysql_error()) ;

$count =3D mysql_num_rows($result);

echo $count;
=20

while($row =3D mysql_fetch_array($result)) {

=20

$name=3Darray($row['name'],0,1);

//$name =3D array('name');

echo " href=3D\"page.php?name=3D" .$name. "\"> $name\n";

}=20
=20

}

=20

$alphabet =3D range('A', 'Z');

foreach ($alphabet as $letter) {

echo '
.. '?letter=3D' . $letter . '">' . $letter . '';

}=20
=20

=20

=20

?>


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Alphabetical pagination

--000e0cd340f0dc2161046eb04335
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

>
> I am trying to make a page that displays a-z like a b c d e etc as links
> then when you click open one it reloads itself and shows only the query
> results that go with that letter...i'm not getting it....I get a page that
> says ARRAY over and over...
>
> What I have so far:
> >
> if(!isset($_SESSION['RestaurantList']))
>
> {
> // List not grabbed yet, so run
> query and store in $_SESSION
>
> $sql = "SELECT
> DISTINCT name FROM restaurants GROUP BY name DESC";
>
> $result = mysql_query($sql) or die(mysql_error()) ;
>
> $count = mysql_num_rows($result);
>
> echo $count;
>
>
> while($row = mysql_fetch_array($result)) {
>
>
>
> $name=array($row['name'],0,1);
>
> //$name = array('name');
>
> echo " > href=\"page.php?name=" .$name. "\"> $name\n";
>
> }
>
>
> }
>
>
>
> $alphabet = range('A', 'Z');
>
> foreach ($alphabet as $letter) {
>
> echo '' . $letter . '';
>
> }
>
>
>
>
>
>
> ?>
>

Well, are you opposed to using Javascript?

//javascript function


//You could make a hidden form field to store the value of the letter
selected.

Re: Alphabetical pagination

On Tue, Jul 14, 2009 at 3:38 PM, Miller,
Terion wrote:
> I am trying to make a page that displays a-z like a b c d e etc as links
> then when you click open one it reloads itself and shows only the query
> results that go with that letter...i'm not getting it....I get a page tha=
t
> says ARRAY over and over...
>
> What I have so far:
>                     =C2=
=A0       >
> if(!isset($_SESSION['RestaurantList']))
>
> {
>                     =C2=
=A0                // List not grab=
bed yet, so run
> query and store in $_SESSION
>
>                     =C2=
=A0                     =
             $sql =3D "SELECT
> DISTINCT name FROM restaurants  GROUP BY name DESC";
>
> $result =3D mysql_query($sql) or die(mysql_error()) ;
>
> $count =3D mysql_num_rows($result);
>
> echo $count;
>
>
> while($row =3D mysql_fetch_array($result)) {
>
>
>
> $name=3Darray($row['name'],0,1);




Why are you setting the value of $name to an array? If you try to echo
$name after this statement (as you are below), PHP will echo the word
"Array".



>
> //$name =3D array('name');
>
>                     =C2=
=A0                     =
             echo " > href=3D\"page.php?name=3D" .$name. "\"> $name\n";
>
> }
>
>
>                     =C2=
=A0                     =
                    =C2=
=A0    }
>
>
>
> $alphabet =3D range('A', 'Z');
>
> foreach ($alphabet as $letter) {
>
> echo ' >
> . '?letter=3D' . $letter . '">' . $letter . '';
>
> }
>
>
>
>
>
>
> ?>

If the list of restaurants is very long, I wouldn't store it in a
session variable. It is the same for every person who visits your
site, so there is little use storing separate redundant copies in
session scope where it will needlessly fill up disk space and/or
memory.

As far as the query is concerned, you could do this:


$index_letter =3D $_GET['letter'];

if (preg_match('/^[A-Za-z]$/', $index_letter)) {

$sql =3D "SELECT DISTINCT name FROM restaurants WHERE name LIKE
'$index_letter%' GROUP BY name DESC";

//....
?>

I would also consider whether you really need the keyword DISTINCT in
the query. In a properly normalized table, name should probably
already be distinct (and constrained by a UNIQUE index on that
column).

Andrew

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Alphabetical pagination

Andrew Ballard wrote:
> On Tue, Jul 14, 2009 at 3:38 PM, Miller,
> Terion wrote:
>> I am trying to make a page that displays a-z like a b c d e etc as links
>> then when you click open one it reloads itself and shows only the query
>> results that go with that letter...i'm not getting it....I get a page that
>> says ARRAY over and over...
>>
>> What I have so far:
>> >>
>> if(!isset($_SESSION['RestaurantList']))
>>
>> {
>> // List not grabbed yet, so run
>> query and store in $_SESSION
>>
>> $sql = "SELECT
>> DISTINCT name FROM restaurants GROUP BY name DESC";
>>
>> $result = mysql_query($sql) or die(mysql_error()) ;
>>
>> $count = mysql_num_rows($result);
>>
>> echo $count;
>>
>>
>> while($row = mysql_fetch_array($result)) {
>>
>>
>>
>> $name=array($row['name'],0,1);
>
>
>
>
> Why are you setting the value of $name to an array? If you try to echo
> $name after this statement (as you are below), PHP will echo the word
> "Array".
>
>
>
>> //$name = array('name');
>>
>> echo " >> href=\"page.php?name=" .$name. "\"> $name\n";
>>
>> }
>>
>>
>> }
>>
>>
>>
>> $alphabet = range('A', 'Z');
>>
>> foreach ($alphabet as $letter) {
>>
>> echo '' . $letter . '';
>>
>> }
>>
>>
>>
>>
>>
>>
>> ?>
>
> If the list of restaurants is very long, I wouldn't store it in a
> session variable. It is the same for every person who visits your
> site, so there is little use storing separate redundant copies in
> session scope where it will needlessly fill up disk space and/or
> memory.
>
> As far as the query is concerned, you could do this:
>
> >
> $index_letter = $_GET['letter'];
>
> if (preg_match('/^[A-Za-z]$/', $index_letter)) {
>
> $sql = "SELECT DISTINCT name FROM restaurants WHERE name LIKE
> '$index_letter%' GROUP BY name DESC";
>
> //....
> ?>
>
> I would also consider whether you really need the keyword DISTINCT in
> the query. In a properly normalized table, name should probably
> already be distinct (and constrained by a UNIQUE index on that
> column).
>
> Andrew
>

Also, since this is MySQL, you need to make sure you make it non case-sensitive by using ILIKE instead of LIKE in that
SELECT statement.

$sql = "SELECT DISTINCT name FROM restaurants WHERE name iLIKE '$index_letter%' GROUP BY name DESC";

--
Jim Lucas

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Alphabetical pagination

On Wednesday 15 July 2009 06:35:04 Jim Lucas wrote:
> Andrew Ballard wrote:
> > On Tue, Jul 14, 2009 at 3:38 PM, Miller,
> >
> > Terion wrote:
> >> I am trying to make a page that displays a-z like a b c d e etc as links
> >> then when you click open one it reloads itself and shows only the query
> >> results that go with that letter...i'm not getting it....I get a page
> >> that says ARRAY over and over...
> >>
> >> What I have so far:
> >> > >>
> >> if(!isset($_SESSION['RestaurantList']))
> >>
> >> {
> >> // List not grabbed yet, so run
> >> query and store in $_SESSION
> >>
> >> $sql = "SELECT
> >> DISTINCT name FROM restaurants GROUP BY name DESC";
> >>
> >> $result = mysql_query($sql) or die(mysql_error()) ;
> >>
> >> $count = mysql_num_rows($result);
> >>
> >> echo $count;
> >>
> >>
> >> while($row = mysql_fetch_array($result)) {
> >>
> >>
> >>
> >> $name=array($row['name'],0,1);
> >
> > Why are you setting the value of $name to an array? If you try to echo
> > $name after this statement (as you are below), PHP will echo the word
> > "Array".
> >
> >> //$name = array('name');
> >>
> >> echo " > >> href=\"page.php?name=" .$name. "\"> $name\n";
> >>
> >> }
> >>
> >>
> >> }
> >>
> >>
> >>
> >> $alphabet = range('A', 'Z');
> >>
> >> foreach ($alphabet as $letter) {
> >>
> >> echo '' . $letter . '';
> >>
> >> }
> >>
> >>
> >>
> >>
> >>
> >>
> >> ?>
> >
> > If the list of restaurants is very long, I wouldn't store it in a
> > session variable. It is the same for every person who visits your
> > site, so there is little use storing separate redundant copies in
> > session scope where it will needlessly fill up disk space and/or
> > memory.
> >
> > As far as the query is concerned, you could do this:
> >
> > > >
> > $index_letter = $_GET['letter'];
> >
> > if (preg_match('/^[A-Za-z]$/', $index_letter)) {
> >
> > $sql = "SELECT DISTINCT name FROM restaurants WHERE name LIKE
> > '$index_letter%' GROUP BY name DESC";
> >
> > //....
> > ?>
> >
> > I would also consider whether you really need the keyword DISTINCT in
> > the query. In a properly normalized table, name should probably
> > already be distinct (and constrained by a UNIQUE index on that
> > column).
> >
> > Andrew
>
> Also, since this is MySQL, you need to make sure you make it non
> case-sensitive by using ILIKE instead of LIKE in that SELECT statement.
>
> $sql = "SELECT DISTINCT name FROM restaurants WHERE name iLIKE
> '$index_letter%' GROUP BY name DESC";
>
> --
> Jim Lucas

You only have to use that if your character set is not latin1 or
latin1_swedish_ci. Using LIKE on those character sets is case-insensitive
too.

--
Thanks,
Ash
http://www.ashleysheridan.co.uk

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Alphabetical pagination

On Wed, Jul 15, 2009 at 3:28 AM, Ashley
Sheridan wrote:
> On Wednesday 15 July 2009 06:35:04 Jim Lucas wrote:
>> Andrew Ballard wrote:
>> > On Tue, Jul 14, 2009 at 3:38 PM, Miller,
>> >
>> > Terion wrote:
>> >> I am trying to make a page that displays a-z like a b c d e etc as li=
nks
>> >> then when you click open one it reloads itself and shows only the que=
ry
>> >> results that go with that letter...i'm not getting it....I get a page
>> >> that says ARRAY over and over...
>> >>
>> >> What I have so far:
>> >>                    =
        >> >>
>> >> if(!isset($_SESSION['RestaurantList']))
>> >>
>> >> {
>> >>                    =
                 // List not =
grabbed yet, so run
>> >> query and store in $_SESSION
>> >>
>> >>                    =
                    =C2=
=A0              $sql =3D "SELECT
>> >> DISTINCT name FROM restaurants  GROUP BY name DESC";
>> >>
>> >> $result =3D mysql_query($sql) or die(mysql_error()) ;
>> >>
>> >> $count =3D mysql_num_rows($result);
>> >>
>> >> echo $count;
>> >>
>> >>
>> >> while($row =3D mysql_fetch_array($result)) {
>> >>
>> >>
>> >>
>> >> $name=3Darray($row['name'],0,1);
>> >
>> > Why are you setting the value of $name to an array? If you try to echo
>> > $name after this statement (as you are below), PHP will echo the word
>> > "Array".
>> >
>> >> //$name =3D array('name');
>> >>
>> >>                    =
                    =C2=
=A0              echo " >> >> href=3D\"page.php?name=3D" .$name. "\"> $name\n";
>> >>
>> >> }
>> >>
>> >>
>> >>                    =
                    =C2=
=A0                     =
     }
>> >>
>> >>
>> >>
>> >> $alphabet =3D range('A', 'Z');
>> >>
>> >> foreach ($alphabet as $letter) {
>> >>
>> >> echo ' >> >>
>> >> . '?letter=3D' . $letter . '">' . $letter . '';
>> >>
>> >> }
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >> ?>
>> >
>> > If the list of restaurants is very long, I wouldn't store it in a
>> > session variable. It is the same for every person who visits your
>> > site, so there is little use storing separate redundant copies in
>> > session scope where it will needlessly fill up disk space and/or
>> > memory.
>> >
>> > As far as the query is concerned, you could do this:
>> >
>> > >> >
>> > $index_letter =3D $_GET['letter'];
>> >
>> > if (preg_match('/^[A-Za-z]$/', $index_letter)) {
>> >
>> >     $sql =3D "SELECT DISTINCT name FROM restaurants WHERE na=
me LIKE
>> > '$index_letter%' GROUP BY name DESC";
>> >
>> > //....
>> > ?>
>> >
>> > I would also consider whether you really need the keyword DISTINCT in
>> > the query. In a properly normalized table, name should probably
>> > already be distinct (and constrained by a UNIQUE index on that
>> > column).
>> >
>> > Andrew
>>
>> Also, since this is MySQL, you need to make sure you make it non
>> case-sensitive by using ILIKE instead of LIKE in that SELECT statement.
>>
>> $sql =3D "SELECT DISTINCT name FROM restaurants WHERE name iLIKE
>> '$index_letter%' GROUP BY name DESC";
>>
>> --
>> Jim Lucas
>
> You only have to use that if your character set is not latin1 or
> latin1_swedish_ci. Using LIKE on those character sets is case-insensitive
> too.
>
> --
> Thanks,
> Ash
> http://www.ashleysheridan.co.uk
>

.... or any of the other *_ci character set collations. ;-) I generally
use utf8_unicode_ci in MySQL.

Andrew

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Alphabetical pagination

At 12:38 PM -0700 7/14/09, Miller, Terion wrote:
>I am trying to make a page that displays a-z like a b c d e etc as links
>then when you click open one it reloads itself and shows only the query
>results that go with that letter...i'm not getting it....I get a page that
>says ARRAY over and over...
>
>What I have so far:
-snip-

Why not have MySQL sort the data instead of using php?

For example (from memory -- use with caution)

SELECT name FROM restaurant ORDER BY name DESC LIMIT $offset, 1

Then just change the offset to go up and down the list.

Cheers,

tedd
--
-------
http://sperling.com http://ancientstones.com http://earthstones.com

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Alphabetical pagination

On 7/15/09 10:30 AM, "Ashley Sheridan" wrote:

On Wednesday 15 July 2009 16:21:22 tedd wrote:
> At 12:38 PM -0700 7/14/09, Miller, Terion wrote:
> >I am trying to make a page that displays a-z like a b c d e etc as links
> >then when you click open one it reloads itself and shows only the query
> >results that go with that letter...i'm not getting it....I get a page th=
at
> >says ARRAY over and over...
> >
> >What I have so far:
>
> -snip-
>
> Why not have MySQL sort the data instead of using php?
>
> For example (from memory -- use with caution)
>
> SELECT name FROM restaurant ORDER BY name DESC LIMIT $offset, 1
>
> Then just change the offset to go up and down the list.
>
> Cheers,
>
> tedd
> --
> -------
> http://sperling.com http://ancientstones.com http://earthstones.com

You could do what Tedd suggested, but use MySQL to actually limit the resul=
ts
it returns you by using a like clause, i.e. WHERE `somefield` LIKE 'a%'.

*ducks to avoid people throwing things at him. I know it's slow!*

--
Thanks,
Ash
http://www.ashleysheridan.co.uk


Hi all thanks for all the suggestions, I really had no idea this was going =
to be so difficult..I am a bit closer..well I have the alphabet showing up =
now I just have to get it to actually pull results..LMAO
Here is my code as it is today, I have by end of day to get it to work...yi=
kes the stress is unbearable, there are a million pagination scripts out th=
ere but I can't find any good alpha ones I can understand.
Ok what I have so far: the top part lists all the restaurant names, the bot=
tom part has the alpha pagination ...neither links to the results of a quer=
y...yet... Bit by bit right?

$sql =3D "SELECT DI=
STINCT name FROM restaurants WHERE name LIKE =
'$index_letter%' GROUP BY name D=
ESC"; =
$result =3D mysql_que=
ry($sql) or die(mysql_error()) ; =
$count =3D mysql_num_rows($result); =
=
while($row =3D mysql_fetch_array($result)) { =
=
=
$name=3D$row['name']; =
=
//prints out restaurant name with link ..... =
echo " me. "\"> $name\n
"; =
} =
=
} //al=
phabetical pagination links =
if (!isset($_GET['letter'])) {$letter =3D "A";} else {$=
letter =3D $_GET['letter'];} =
echo '

"; =
=
$query=3D"SELECT * FROM restaurants W=
HERE name LIKE '".$letter."%'"; =
=
=
?>

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Alphabetical pagination

On Wednesday 15 July 2009 16:21:22 tedd wrote:
> At 12:38 PM -0700 7/14/09, Miller, Terion wrote:
> >I am trying to make a page that displays a-z like a b c d e etc as links
> >then when you click open one it reloads itself and shows only the query
> >results that go with that letter...i'm not getting it....I get a page that
> >says ARRAY over and over...
> >
> >What I have so far:
>
> -snip-
>
> Why not have MySQL sort the data instead of using php?
>
> For example (from memory -- use with caution)
>
> SELECT name FROM restaurant ORDER BY name DESC LIMIT $offset, 1
>
> Then just change the offset to go up and down the list.
>
> Cheers,
>
> tedd
> --
> -------
> http://sperling.com http://ancientstones.com http://earthstones.com

You could do what Tedd suggested, but use MySQL to actually limit the results
it returns you by using a like clause, i.e. WHERE `somefield` LIKE 'a%'.

*ducks to avoid people throwing things at him. I know it's slow!*

--
Thanks,
Ash
http://www.ashleysheridan.co.uk

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Alphabetical pagination

At 4:30 PM +0100 7/15/09, Ashley Sheridan wrote:
>On Wednesday 15 July 2009 16:21:22 tedd wrote:
>> At 12:38 PM -0700 7/14/09, Miller, Terion wrote:
>> >I am trying to make a page that displays a-z like a b c d e etc as links
>> >then when you click open one it reloads itself and shows only the query
>> >results that go with that letter...i'm not getting it....I get a page that
>> >says ARRAY over and over...
>> >
>> >What I have so far:
>>
>> -snip-
>>
>> Why not have MySQL sort the data instead of using php?
>>
>> For example (from memory -- use with caution)
>>
>> SELECT name FROM restaurant ORDER BY name DESC LIMIT $offset, 1
>>
>> Then just change the offset to go up and down the list.
>>
>> Cheers,
>>
>> tedd
>> --
>> -------
>> http://sperling.com http://ancientstones.com http://earthstones.com
>
>You could do what Tedd suggested, but use MySQL to actually limit the results
>it returns you by using a like clause, i.e. WHERE `somefield` LIKE 'a%'.
>
>*ducks to avoid people throwing things at him. I know it's slow!*
>
>--
>Thanks,
>Ash


Ash:

My solution on this is simply to sort all the names and then pull
them out one by one depending upon where they appear in the sort. I
don't really see the need for LIKE to do this unless the user is
entering a guess. In such case, then you want to provide them with
your best fit.

Here's an example of both:

http://php1.net/a/edit-db-demo/index.php

The user can step through the database, which is initially sorted on
last name. If the user picks to sort on email, then the sort is done
as per email. In both cases, the data is stepped through sequentially
(Alphabetical pagination) by using an offset and limit combination.

However, if the user clicks on an alphabetical tab, then I use a LIKE
to place them (their pagination) at the position of that sort at that
character. That's where a LIKE would work well.

Cheers,

tedd

--
-------
http://sperling.com http://ancientstones.com http://earthstones.com

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Alphabetical pagination

On Wed, Jul 15, 2009 at 11:30 AM, Ashley
Sheridan wrote:
> On Wednesday 15 July 2009 16:21:22 tedd wrote:
>> At 12:38 PM -0700 7/14/09, Miller, Terion wrote:
>> >I am trying to make a page that displays a-z like a b c d e etc as link=
s
>> >then when you click open one it reloads itself and shows only the query
>> >results that go with that letter...i'm not getting it....I get a page t=
hat
>> >says ARRAY over and over...
>> >
>> >What I have so far:
>>
>> -snip-
>>
>> Why not have MySQL sort the data instead of using php?
>>
>> For example (from memory -- use with caution)
>>
>> SELECT name FROM restaurant ORDER BY name DESC LIMIT $offset, 1
>>
>> Then just change the offset to go up and down the list.
>>
>> Cheers,
>>
>> tedd
>> --
>> -------
>> http://sperling.com  http://ancientstones.com  http://earthsto=
nes.com
>
> You could do what Tedd suggested, but use MySQL to actually limit the res=
ults
> it returns you by using a like clause, i.e. WHERE `somefield` LIKE 'a%'.
>
> *ducks to avoid people throwing things at him. I know it's slow!*
>
> --
> Thanks,
> Ash
> http://www.ashleysheridan.co.uk
>

Why would that be slow? Using LIKE isn't always a bad thing. In this
case, the LIKE condition begins with a constant rather than a
wildcard, so it should perform well. It can even benefit from an index
on `somefield` if one exists.

I only see a couple issues with tedd's query:

1) As written, it only returns one row. To get it to return a list,
you'd have to call it repeatedly inside a for...loop where $offset
increments begins at some value and increments/decrements to an ending
value. But then he did say "from memory -- use with caution". The
general idea is correct.

2) It implements numeric pagination, which is usually based on a
fixed number of rows per page. The OP wanted alphabetical pagination
(like an address book) with each page containing all entries that
begin with the selected letter.


Andrew

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Alphabetical pagination

At 8:29 AM -0700 7/15/09, Miller, Terion wrote:
>
>Hi all thanks for all the suggestions, I really had no idea this was
>going to be so difficult..

I think you are making it more difficult than it has to be.

Please review what I said and try it out.

Cheers,

tedd



--
-------
http://sperling.com http://ancientstones.com http://earthstones.com

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Alphabetical pagination

On Wednesday 15 July 2009 16:46:53 Andrew Ballard wrote:
> On Wed, Jul 15, 2009 at 11:30 AM, Ashley
>
> Sheridan wrote:
> > On Wednesday 15 July 2009 16:21:22 tedd wrote:
> >> At 12:38 PM -0700 7/14/09, Miller, Terion wrote:
> >> >I am trying to make a page that displays a-z like a b c d e etc as
> >> > links then when you click open one it reloads itself and shows only
> >> > the query results that go with that letter...i'm not getting it....I
> >> > get a page that says ARRAY over and over...
> >> >
> >> >What I have so far:
> >>
> >> -snip-
> >>
> >> Why not have MySQL sort the data instead of using php?
> >>
> >> For example (from memory -- use with caution)
> >>
> >> SELECT name FROM restaurant ORDER BY name DESC LIMIT $offset, 1
> >>
> >> Then just change the offset to go up and down the list.
> >>
> >> Cheers,
> >>
> >> tedd
> >> --
> >> -------
> >> http://sperling.com  http://ancientstones.com  http://earths=
tones.com
> >
> > You could do what Tedd suggested, but use MySQL to actually limit the
> > results it returns you by using a like clause, i.e. WHERE `somefield`
> > LIKE 'a%'.
> >
> > *ducks to avoid people throwing things at him. I know it's slow!*
> >
> > --
> > Thanks,
> > Ash
> > http://www.ashleysheridan.co.uk
>
> Why would that be slow? Using LIKE isn't always a bad thing. In this
> case, the LIKE condition begins with a constant rather than a
> wildcard, so it should perform well. It can even benefit from an index
> on `somefield` if one exists.
>
> I only see a couple issues with tedd's query:
>
> 1) As written, it only returns one row. To get it to return a list,
> you'd have to call it repeatedly inside a for...loop where $offset
> increments begins at some value and increments/decrements to an ending
> value. But then he did say "from memory -- use with caution". The
> general idea is correct.
>
> 2) It implements numeric pagination, which is usually based on a
> fixed number of rows per page. The OP wanted alphabetical pagination
> (like an address book) with each page containing all entries that
> begin with the selected letter.
>
>
> Andrew

I just had a query doing the same thing one time, and that did take it's ti=
me=20
(about 2-3 seconds) but it did have a few million records to look at, so I=
=20
can understand why it was slow!

And it was on MSSQL, with no indexes set up :( I nearly cried when I saw wh=
at=20
I was dealing with!

=2D-=20
Thanks,
Ash
http://www.ashleysheridan.co.uk

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Alphabetical pagination

SET @letter :=3D 'B';
SELECT name FROM table WHERE SUBSTRING(name, 1) == @letter;
SELECT name FROM table WHERE name like concat(@letter, '%');

I don't know a faster way to do it

On Wed, Jul 15, 2009 at 12:57 PM, Ashley
Sheridan wrote:
> On Wednesday 15 July 2009 16:46:53 Andrew Ballard wrote:
>> On Wed, Jul 15, 2009 at 11:30 AM, Ashley
>>
>> Sheridan wrote:
>> > On Wednesday 15 July 2009 16:21:22 tedd wrote:
>> >> At 12:38 PM -0700 7/14/09, Miller, Terion wrote:
>> >> >I am trying to make a page that displays a-z like a b c d e etc as
>> >> > links then when you click open one it reloads itself and shows only
>> >> > the query results that go with that letter...i'm not getting it....=
I
>> >> > get a page that says ARRAY over and over...
>> >> >
>> >> >What I have so far:
>> >>
>> >> -snip-
>> >>
>> >> Why not have MySQL sort the data instead of using php?
>> >>
>> >> For example (from memory -- use with caution)
>> >>
>> >> SELECT name FROM restaurant ORDER BY name DESC LIMIT $offset, 1
>> >>
>> >> Then just change the offset to go up and down the list.
>> >>
>> >> Cheers,
>> >>
>> >> tedd
>> >> --
>> >> -------
>> >> http://sperling.com =A0http://ancientstones.com =A0http://earthstones=
..com
>> >
>> > You could do what Tedd suggested, but use MySQL to actually limit the
>> > results it returns you by using a like clause, i.e. WHERE `somefield`
>> > LIKE 'a%'.
>> >
>> > *ducks to avoid people throwing things at him. I know it's slow!*
>> >
>> > --
>> > Thanks,
>> > Ash
>> > http://www.ashleysheridan.co.uk
>>
>> Why would that be slow? Using LIKE isn't always a bad thing. In this
>> case, the LIKE condition begins with a constant rather than a
>> wildcard, so it should perform well. It can even benefit from an index
>> on `somefield` if one exists.
>>
>> I only see a couple issues with tedd's query:
>>
>> 1) As written, it only returns one row. To get it to return a list,
>> you'd have to call it repeatedly inside a for...loop where $offset
>> increments begins at some value and increments/decrements to an ending
>> value. But then he did say "from memory -- use with caution". The
>> general idea is correct.
>>
>> 2) =A0It implements numeric pagination, which is usually based on a
>> fixed number of rows per page. The OP wanted alphabetical pagination
>> (like an address book) with each page containing all entries that
>> begin with the selected letter.
>>
>>
>> Andrew
>
> I just had a query doing the same thing one time, and that did take it's =
time
> (about 2-3 seconds) but it did have a few million records to look at, so =
I
> can understand why it was slow!
>
> And it was on MSSQL, with no indexes set up :( I nearly cried when I saw =
what
> I was dealing with!
>
> --
> Thanks,
> Ash
> http://www.ashleysheridan.co.uk
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>



--=20
Martin Scotta

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Alphabetical pagination

--snip----


How do I write the for each loop in here:

//alphabetical pagination links =
if (!isset($_GET['letter'])) {$letter =3D "A";} =
else {$letter =3D $_GET['letter'];} =
echo '